#coding:utf8
from sqlprocess import connDB

class Tournament(connDB):
	def getTM(self):
		sql  = ' select * from hot_event '
		
		return self.client.mselect(sql,())
	def getByfilter(self,table_name,params, field = None, offset = None, limit = None, order = None):
		return self.select_all(table_name,params,field = field, offset = offset, limit  = limit, order_by = order)


	def filter(self,table_name,params):
		pass


	def get_event_foreshow(self,params,game_id):
		sql = '''
		SELECT ef.event_id,ef.start_time,gm.events_title,gm.game_id,ef.foreshow_type, gm.event_type_id, gm.icon as event_icon,gm.type,
		gm.apply_start_time, gm.apply_end_time, gm.game_start_time, gm.game_end_time FROM 
		event_foreshow  as ef left join(select id,events_title,game_id,event_type_id , icon,type,apply_start_time
		, apply_end_time,game_start_time, game_end_time from hot_event ) as gm 
		on ef.event_id = gm.id  where year  = %s and month =%s

		'''

		if game_id:
			sql += ' game_id = %s '
			params.append(game_id)
		return self.client.mselect(sql,tuple(params))
	## 战队排行榜
	def GetGameTeam(self,params,offset,limit):
		table_name = 'team_all_rank'
		join_where = ' left '
		text_array = [' gt.check_status  = %s ']
		val_array = [1]
		if params.get("_type").encode("utf8")=='w':
			table_name = 'team_w_rank'
			join_where ='right'
		sql = '''
    	SELECT gt.id,gt.team_type,gt.recruit,gt.team_name,gt.team_icon,gt.has_member,tmr.join_game_num,tmr.competition_point,gt.max_member FROM game_team  as gt 
    	%s join (select team_id,sum(join_game_num) as join_game_num,sum(competition_point) as competition_point from %s %s group by team_id) as tmr 
    	on gt.id = tmr.team_id %s order by  tmr.competition_point desc,gt.has_member desc,gt.id limit %s,%s 
		'''
		t_sql = '''
		select count(*)count from (SELECT gt.id,tmr.team_id FROM game_team  as gt 
    	%s join (select team_id from %s %s group by team_id) as tmr 
    	on gt.id = tmr.team_id %s )a 
		'''
		where = ''
		if params.get('team_name'):
			text_array.append(' gt.team_name like  %s')
			val_array.append(params.get('team_name')+'%')
		if text_array:
			where = ' where ' + ' and '.join(text_array)
		_where = ''
		if params.get('game_id'):
			_where = ' where game_id = %s ' % params.get('game_id')
			join_where = ' right '
		sql = sql % (join_where,table_name,_where,where,offset,limit)
		t_sql = t_sql % (join_where,table_name,_where,where)
		return self.client.mselect(sql,tuple(val_array)),self.client.mselect(t_sql,(val_array))


	def TeamPerformanceData(self,params):
		table_name = 'team_w_rank'
		if params.get('_type','')=='all':
			table_name = 'team_all_rank'
		text_array =[]
		val_array = []
		sql = '''
		SELECT gt.id,sum(tr.join_game_num)join_game_num,sum(competition_point)competition_point,gt.team_name,gt.team_icon,gt.has_member,gt.max_member,gt.team_type 
		FROM %s as tr left join (select id,team_name,team_icon,has_member,max_member,team_type from  game_team where dissolve = 0 and check_status = 1) as gt 
		on tr.team_id = gt.id %s  group by tr.team_id  order by competition_point desc  limit %s,%s
		'''
		where = ''
		if params.get('game_id'):
			text_array.append(' tr.game_id = %s')
			val_array.append(params.get('game_id'))
		if text_array:
			where = ' where ' + ' and '.join(text_array)
		sql = sql % (table_name,where,params.get('offset',0), params.get('limit',10))
		return self.client.mselect(sql,tuple(val_array))


	def GetUserToEvent(self,team_id):

		sql ='''
    	SELECT users.nickname, users.icon,users.id FROM user_team as ut left join 
    	(select id,icon,nickname from users) as users on ut.user_id = users.id where ut.team_id=%s;

		'''
		return self.client.mselect(sql,(team_id,))

	def GetEventPerformance(self,params,offset,limit):
		text_array =[]
		val_array = []
		sql = '''
		SELECT * FROM hot_event %s order by type , total_bonus desc , event_type_id desc limit %s,%s;

		'''
		where = ''
		if params.get('game_id'):
			text_array.append(' game_id = %s')
			val_array.append(params.get('game_id'))
		if params.get('award_type'):
			text_array.append('award_type = %s ')
			val_array.append(params.get('award_type'))
		if text_array:
			where = ' where ' + ' and '.join(text_array)	
		sql = sql % (where,offset,limit)
		return self.client.mselect(sql,tuple(val_array))

	def getteamgoodgame(self,team_id):

		sql = '''
		SELECT game.icon FROM teamgoodgame as tgg left join 
		(select id,icon from game) as game on tgg.game_id=game.id where tgg.team_id=%s;
		'''	
		return self.client.mselect(sql,(team_id))

	def getstarleader(self,params= dict()):
		sql ='''
		SELECT users.id,users.nickname,users.icon FROM user_team as ut left 
		join(select id,nickname,icon from users) as users on ut.user_id=users.id where ut.star_team_leader = 1 and ut.team_position =2 limit 0,10;

		'''
		return self.client.mselect(sql,())

	## 根据战队id查找相关信息
	def GetTeamDBYID(self,team_id):

		sql = '''
		select gt.team_leader_id,gt.recruit,gt.id,gt.team_name, gt.team_icon, gt.team_details, gt.has_member, gt.max_member,gt.team_type, 
		user.nickname,gt.like_num,gt.focus_on from game_team as gt left join(select id,nickname from users) as user on 
		gt.team_leader_id = user.id where gt.id=%s

		'''
		return self.client.mselect(sql,(team_id))

	def team_user(self,params):
		text_array =[]
		val_array = []
		sql = '''
		SELECT users.id as user_id,users.nickname,users.icon,ut.team_position, ut.id as user_team_id FROM user_team as ut left 
		join(select id,nickname,icon from users) as users on ut.user_id=users.id  %s order by ut.team_position desc

		'''

		where = ''
		if params.get('team_id'):
			text_array.append(' ut.team_id = %s')
			val_array.append(params.get('team_id'))
		if params.get('nickname'):
			text_array.append(' users.nickname = %s ')
			val_array.append(params.get('nickname'))
		if text_array:
			where = ' where ' + ' and '.join(text_array)	
		sql = sql % where
		return self.client.mselect(sql,tuple(val_array))

	def GetTeamEventData(self,team_id, offset, limit):
		sql = '''
    	select het.*,aut.id as auto_id from auth_event as aut left join 
    	(select * from hot_event) as het on aut.event_id= het.id where aut.team_id=%s limit %s,%s;
		'''

		t_sql = '''
    	select count(1)count from auth_event as aut left join 
    	(select * from hot_event) as het on aut.event_id= het.id where aut.team_id=%s;
		'''
		return self.client.mselect(sql,(team_id, offset, limit)),self.client.mselect(t_sql,(team_id))[0].get('count')

	def aoth_event_user(self,auto_event_id):
		sql  ='''
			SELECT users.id,users.icon ,aut.id as auth_event_to_user_id, aut.auto_event_id  FROM auth_event_to_user as aut left 
			join(select * from users) as users on aut.user_id = users.id where aut.auto_event_id = %s
		'''
		return self.client.mselect(sql,(auto_event_id))
	## 获取某个赛事中参赛的队员
	def JoinEventUser(self,params):
		sql  ='''
		SELECT auser.user_id ,auser.auto_event_id  FROM auth_event as aut left join 
		(select * from auth_event_to_user) as auser on aut.id = auser.auto_event_id where aut.event_id = %s and aut.team_id=%s;
		'''
		return self.client.mselect(sql,(params.get('event_id'),params.get('team_id')))

	def teamhonor(self,params,offset,limit):
		sql = '''
		select * from  (SELECT th.bonus,th.bonus_type,aut.event_id,th.rank,th.create_time FROM team_honor as th left 
		join(select * from  auth_event) as aut on th.auto_event_id=aut.id where aut.team_id=%s limit %s,%s) as us 
		left join (select id,game_id,event_type_id,events_title from hot_event) as ht 
		on us.event_id=ht.id

		'''
		t_sql = '''
		select count(1)count from  (SELECT th.bonus,th.bonus_type,aut.event_id,th.rank FROM team_honor as th left 
		join(select * from  auth_event) as aut on th.auto_event_id=aut.id where aut.team_id=%s ) as us 
		left join (select id,game_id,event_type_id,events_title from hot_event) as ht 
		on us.event_id=ht.id

		'''
		team_id = params.get('team_id')
		return self.client.mselect(sql,(team_id,offset, limit)),self.client.mselect(t_sql,(team_id))[0].get('count')

	def check_u_team(self,params,offset, limit):
		sql = '''
		SELECT apt.id,apt.detail, users.nickname, users.icon FROM apply_team as apt left join 
		(select * from users where status = 0 ) as users on apt.user_id = users.id where apt.status = 0  and apt.team_id = %s limit %s, %s
		'''
		t_sql = '''
		SELECT count(1)count FROM apply_team as apt left join 
		(select * from users where status = 0 ) as users on apt.user_id = users.id where apt.status = 0  and apt.team_id = %s 
		'''
		return self.client.mselect(sql, (params.get('team_id'),int(offset), int(limit))),self.client.mselect(t_sql,(params.get('team_id')))[0].get('count')

	## 获取用户个人关注的信息
	def UserFocus(self,params,count = False):
		sql = '''
		SELECT gt.team_icon, gt.team_name,fo.id, gt.id as team_id FROM focus_on as fo left join 
		(select id,team_icon, team_name from game_team) as gt on fo.team_id = gt.id where user_id = %s limit %s ,%s
		'''
		t_count = 0
		if count:
			t_sql = '''
				SELECT count(1)count FROM focus_on as fo left join 
				(select id,team_icon, team_name from game_team) as gt on fo.team_id = gt.id where user_id = %s 

			'''
			t_count = self.client.mselect(t_sql,(params.get('user_id')))
		return self.client.mselect(sql, (params.get('user_id'),params.get('offset'),params.get('limit'))),t_count

	## 获取个人参与的赛事
	def UserEvent(self,params):
		sql = '''
		SELECT * FROM hot_event as aut where  exists 
		(select * from auth_event_to_user as hot where hot.event_id = aut.id and hot.user_id=%s order by aut.id desc limit %s,%s)


		'''
		t_sql = '''
		SELECT count(1)count FROM hot_event as aut where  exists 
		(select * from auth_event_to_user as hot where hot.event_id = aut.id and hot.user_id=%s )
		'''

		return self.client.mselect(sql, (params.get('user_id'), params.get('offset'), params.get('limit'))),self.client.mselect(t_sql,(params.get('user_id')))[0].get('count')

	## 通过用户id来获取战队信息
	def user_team_m(self, params):
		sql = '''
		 select a.rank, hot.events_title,hot.event_type_id, hot.game_id , a.create_time from  
		 (SELECT aut.event_id,th.rank,th.create_time FROM e_sport.auth_event_to_user as aut right 
		 join(select * from team_honor) as th on aut.auto_event_id = th.auto_event_id where user_id  = %s limit %s,%s) as a left join 
		 (select id,events_title,event_type_id,game_id from hot_event) as hot on a.event_id= hot.id
		'''
		return self.client.mselect(sql, (params.get('user_id'),params.get('offset'),params.get('limit')))

	## 根据用户id查找战队
	def GetTeamByUserId(self,params):
		sql ='''

		SELECT * FROM game_team where id = (select team_id from user_team where user_id = %s limit 1);
		'''

		return self.client.mselect(sql,(params.get('user_id')))

	## 根据用户个人id和战队id获取个人荣誉
	def GetHonorById(self,params,offset, limit):
		sql = '''
		SELECT * FROM team_honor as th right join (select * from auth_event_to_user where user_id = %s limit %s,%s) 
		as auth on th.auto_event_id=auth.auto_event_id where exists (select team_id from user_team where user_id = %s);

		'''
		t_sql = '''
		SELECT count(1)count FROM team_honor as th right join (select auto_event_id from auth_event_to_user where user_id = %s ) 
		as auth on th.auto_event_id=auth.auto_event_id where exists (select team_id from user_team where user_id = %s);
		'''
		return self.client.mselect(sql, (params.get('user_id'), offset, limit ,params.get('user_id'))),self.client.mselect(t_sql, (params.get('user_id'),params.get('user_id')))[0].get('count')

	def GetEventById(self,params):
		sql = '''
		SELECT id,game_id,events_title,apply_start_time,apply_end_time,event_type_id,
		max_teams, has_teams, total_bonus, game_start_time, game_end_time, introduce,rule,
		award_type, award_name FROM hot_event where id = %s
		'''
		return self.client.mselect(sql, (params.get('event_id')))

	def GetAwardByEvntId(self,event_id):
		sql ='''
			SELECT award,rank FROM event_award where event_id= %s order by rank
		'''
		return self.client.mselect(sql ,(event_id))

	def persHonor(self, user_id,offset, limit):
		sql = '''
		SELECT auth.event_id,th.* FROM auth_event_to_user as auth left join 
		(select auto_event_id,bonus_type, rank,bonus, create_time from team_honor ) as th on 
		auth.auto_event_id=th.auto_event_id where auth.user_id= %s limit %s, %s

		'''

		return self.client.mselect(sql, (user_id, offset, limit ))
	## 检查是否需要安排赛事
	def checkPlanEvent(self,apply_end_time, game_start_time):
		sql = '''
		SELECT * FROM hot_event where apply_end_time <=%s and game_start_time >=%s and status = 0

		'''
		return self.client.mselect(sql,(apply_end_time, game_start_time))

	def check_t_g_c(self, game_id, event_id):

		sql  = '''
		select aa.team_id,rank.competition_point from auth_event as aa left join(select team_id,sum(competition_point) as competition_point from team_w_rank where game_id = %s
		group by team_id) as rank on aa.team_id=rank.team_id where aa.event_id=%s order by rank.competition_point desc;

		'''
		return self.client.mselect(sql,(game_id, event_id))

	## 获取赛事的所有比赛安排
	def EventMatch(self,event_id):
		sql ='''
		SELECT em.*,gt.team_name,gt.team_icon FROM event_match as em left join 
		(select id,team_icon,team_name from  game_team) as gt on em.team_id = gt.id where em.event_id =%s;
		'''
		return self.client.mselect(sql, (event_id))
TM = Tournament()




